# Author: tang
# Date: 2024-05-17
# Description: 新架构存储程序
# Previous version: 2.x
# Modification:
#     2024-11-19: 融合新架构3.0，修改部分存储字段，图谱弃用mongodb存储，改用tdms文件存储

# 标准库
import json
import logging
import traceback
import uuid
import time
import threading
from datetime import datetime

# 第三方库
import redis
import pymysql

# 自建库
import public_func as pf
from sftp_func import *
from common_vars import DeviceSix

logging.basicConfig(format='%(asctime)s-%(filename)s-[line:%(lineno)d]-%(levelname)s: %(message)s', level=logging.DEBUG)
# logging.basicConfig(format='%(asctime)s-%(filename)s-[line:%(lineno)d]-%(levelname)s: %(message)s', level=logging.ERROR,
#                     filename='store.log')

MYSQL_CONN = pf.mysql_connect()  # mysql连接
REDIS_CONN = pf.redis_connect()  # redis连接


class StoreService(threading.Thread):
    """存储服务"""

    def __init__(self, redis_conn, mysql_conn):
        super().__init__()
        self.redis_conn = redis_conn
        self.mysql_conn = mysql_conn

    # @staticmethod
    def dev_ch_register(self,fk_bus_register_id, dev_info, create_time, cursor):
        """
        设备与通道注册
        Args:
            fk_bus_register_id: bus_register表主键id
            dev_info: 设备信息
            create_time: 创建时间
            cursor: mysql游标

        Returns:

        """
        sql = 'select device_code from device_register'
        cursor.execute(sql)
        dev_data = cursor.fetchall()
        dev_list = [i[0] for i in dev_data]
        dept_id =dev_info['dept_id']
        logging.debug(dev_list)
        if dev_info['device_code'] in dev_list:
            # 设备注册
            # register_guid = str(uuid.uuid4()).replace('-', '')
            # sql = 'insert into device_register values(null,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
            # val = (fk_bus_register_id, None, None, None, dev_info['device_type'], dev_info['slave_id'],
            #        dev_info['device_code'], register_guid, 1, None, 0, create_time, None)
            # cursor.execute(sql, val)

            sql_channel = ''' select DISTINCT channel_type from channel_register where device_code='%s' '''%(dev_info['device_code'])
            cursor.execute(sql_channel)
            channel_data = cursor.fetchall()
            
            # if len(channel_data) == 0: #不存在任何通道，进行注册
            #     pass
            # elif len(channel_data) == 1: #存在设备通道，不用注册（非测温），看测温通道存在否
            #     pass
            # elif len(channel_data) == 2: #存在测温通道，判断测温通道数量是否相同
            #     pass
            # else: #存在其他通道
            #     return
            # channeltype_list = [i[0] for i in channel_data]
            # if len(channeltype_list) == 0:
            # if 'temp' in channeltype_list: #温度通道
            #     pass
            # else:#其他类型通道
            #     pass
            sql = 'insert into channel_register values(null,null,null,null,%s,null,%s,%s,%s,null,%s,null,null,0,null,null,%s)'
            # 八通道注册
            if len(channel_data) == 0:#不存在任何通道，进行注册
                
                if dev_info['device_type'] == '01':
                    channel_type = 'uhf'
                    val_01 = []
                    for channel_number in range(1, 9):
                        channel_guid = str(uuid.uuid4()).replace('-', '')
                        val = (create_time, channel_guid, dev_info['device_code'], channel_number, channel_type,dept_id)
                        val_01.append(val)
                    cursor.executemany(sql, val_01)
                elif dev_info['device_type'] == '02':
                    channel_type = 'hfct'
                    val_02 = []
                    for channel_number in range(1, 4):
                        channel_guid = str(uuid.uuid4()).replace('-', '')
                        val = (create_time, channel_guid, dev_info['device_code'], channel_number, channel_type,dept_id)
                        val_02.append(val)
                    cursor.executemany(sql, val_02)
                elif dev_info['device_type'] == '04':
                    val_04 = []
                    channel_guid = str(uuid.uuid4()).replace('-', '')
                    val = (create_time, channel_guid, dev_info['device_code'], 1, 'aa',dept_id)
                    val_04.append(val)
                    channel_guid = str(uuid.uuid4()).replace('-', '')
                    val = (create_time, channel_guid, dev_info['device_code'], 1, 'tev',dept_id)
                    val_04.append(val)
                    cursor.executemany(sql, val_04)
                elif dev_info['device_type'] == '06':
                    val_06 = []
                    channel_guid = str(uuid.uuid4()).replace('-', '')
                    val = (create_time, channel_guid, dev_info['device_code'], 1, 'aa',dept_id)
                    val_06.append(val)
                    channel_guid = str(uuid.uuid4()).replace('-', '')
                    val = (create_time, channel_guid, dev_info['device_code'], 1, 'tev',dept_id)
                    val_06.append(val)
                    channel_guid = str(uuid.uuid4()).replace('-', '')
                    val = (create_time, channel_guid, dev_info['device_code'], 1, 'uhf',dept_id)
                    val_06.append(val)
                    cursor.executemany(sql, val_06)
                elif dev_info['device_type'] == '12':
                    channel_type = 'cc'
                    val_12 = []
                    for channel_number in range(1, 5):
                        channel_guid = str(uuid.uuid4()).replace('-', '')
                        val = (create_time, channel_guid, dev_info['device_code'], channel_number, channel_type,dept_id)
                        val_12.append(val)
                    cursor.executemany(sql, val_12)

                elif dev_info['device_type'] == '27':  # todo 有需求再完善
                    val_27 = []
                    channel_guid = str(uuid.uuid4()).replace('-', '')
                    val = (None, dev_info['device_code'], 1, channel_guid, None,
                        None, 'voltage', None, None, 0, create_time, None)
                    val_27.append(val)
                    channel_guid = str(uuid.uuid4()).replace('-', '')
                    val = (None, dev_info['device_code'], 1, channel_guid, None,
                        None, 'tev', None, None, 0, create_time, None)
                    val_27.append(val)
                    channel_guid = str(uuid.uuid4()).replace('-', '')
                    val = (None, dev_info['device_code'], 1, channel_guid, None,
                        None, 'temp', None, None, 0, create_time, None)  # 环境温度
                    val_27.append(val)
                    channel_guid = str(uuid.uuid4()).replace('-', '')
                    val = (None, dev_info['device_code'], 2, channel_guid, None,
                        None, 'temp', None, None, 0, create_time, None)  # 电缆温度
                    val_27.append(val)
                    channel_guid = str(uuid.uuid4()).replace('-', '')
                    val = (None, dev_info['device_code'], 1, channel_guid, None,
                        None, 'water', None, None, 0, create_time, None)
                    val_27.append(val)
                    cursor.executemany(sql, val_27)
                self.set_default_parameter(dev_info)

            # 温度通道注册
            if len(channel_data) == 1  or len(channel_data) == 0: #注册测温通道
                if dev_info['temp_numb'] > 0:  # 温度
                    channel_type = 'temp'
                    val_temp = []
                    for channel_number in range(1, dev_info['temp_numb'] + 1):
                        channel_guid = str(uuid.uuid4()).replace('-', '')
                        val = (create_time, channel_guid, dev_info['device_code'], channel_number, channel_type,dept_id)
                        val_temp.append(val)
                    cursor.executemany(sql, val_temp)
                    self.set_add_multiple_default_tempature_parameter(dev_info)
                    
            else: #判断是否有修改过temp_numb 个数
                if dev_info['temp_numb'] > 0:  # 温度
                    channel_type = 'temp'
                    sql_channel_temp = ''' select count(channel_type) from channel_register where device_code='%s' and channel_type='temp' '''%(dev_info['device_code'])
                    cursor.execute(sql_channel_temp)
                    channel_temp_data = cursor.fetchone()
                    if channel_temp_data[0]!=0:
                        if int(channel_temp_data[0])>=int(dev_info['temp_numb']):
                            pass
                        else:
                            val_temp_add = []
                            for channel_number in range(int(channel_temp_data[0])+1,int(dev_info['temp_numb'])+1):
                                channel_temp_guid = str(uuid.uuid4()).replace('-', '')
                                val = (create_time, channel_temp_guid, dev_info['device_code'], channel_number, channel_type,dept_id)
                                val_temp_add.append(val)
                                self.set_add_Single_default_tempature_parameter(dev_info,channel_number)
                            cursor.executemany(sql, val_temp_add)
    @staticmethod
    def alarm_operate(cursor, device_type, device_code, channel_number, channel_type, alarm_lv, ch_data,
                      dev_para_dict, alarm_update_val, alarm_insert_val, create_time):
        """
        报警逻辑
        Args:
            cursor: 数据库游标
            device_type: 设备类型
            device_code: 设备编号
            channel_number: 通道号
            channel_type: 通道类型
            alarm_lv: 报警级别
            ch_data: 各通道数据
            dev_para_dict: 设备参数
            alarm_update_val: mysql报警更新操作value
            alarm_insert_val: mysql报警插入操作value
            create_time: 创建时间

        Returns: None

        """
        sql = 'select id,handle_status,alarm_counts from alarm_list where device_code=%s and channel_number=%s and channel_type=%s order by id desc limit 1'
        val = (device_code, channel_number, channel_type)
        cursor.execute(sql, val)
        alarm_res = cursor.fetchone()
        logging.debug(alarm_res)
        content = ''
        alarm_information = ''
        if device_type == '01':
            if channel_type == 'uhf':
                content = json.dumps({"amplitude": ch_data['amplitude'],
                                      "frequency": ch_data['frequency'],
                                      "energy": ch_data['energy']})
                alarm_information = json.dumps(
                    {"upper_alarm_amplitude": dev_para_dict['upper_alarm_amplitude_uhf_ch%s' % channel_number],
                     "upper_alarm_frequency": dev_para_dict['upper_alarm_frequency_uhf_ch%s' % channel_number],
                     "trend_alarm_amplitude": dev_para_dict['trend_alarm_amplitude_uhf_ch%s' % channel_number],
                     "trend_alarm_frequency": dev_para_dict['trend_alarm_frequency_uhf_ch%s' % channel_number],
                     "alarm_strategy": dev_para_dict['alarm_strategy_uhf_ch%s' % channel_number]})
            elif channel_type == 'temp':
                content = json.dumps({"temperature": ch_data['temp']})
                alarm_information = json.dumps(
                    {"upper_alarm_temp": dev_para_dict['upper_alarm_temp_ch%s' % channel_number],
                     "trend_alarm_temp": dev_para_dict['trend_alarm_temp_ch%s' % channel_number],
                     "alarm_strategy_temp": dev_para_dict['alarm_strategy_temp_ch%s' % channel_number]})
        elif device_type == '02':
            content = json.dumps({"amplitude": ch_data['amplitude'],
                                  "frequency": ch_data['frequency'],
                                  "energy": ch_data['energy']})
            alarm_information = json.dumps(
                {"upper_alarm_amplitude": dev_para_dict['upper_alarm_amplitude_hfct_ch%s' % channel_number],
                 "upper_alarm_frequency": dev_para_dict['upper_alarm_frequency_hfct_ch%s' % channel_number],
                 "trend_alarm_amplitude": dev_para_dict['trend_alarm_amplitude_hfct_ch%s' % channel_number],
                 "trend_alarm_frequency": dev_para_dict['trend_alarm_frequency_hfct_ch%s' % channel_number],
                 "alarm_strategy": dev_para_dict['alarm_strategy_hfct_ch%s' % channel_number]})
        elif device_type == '04':
            if channel_type == 'aa':
                content = json.dumps(
                    {"aa_peak_amp": ch_data['aa_peak_amp'], "aa_mean_value": ch_data['aa_mean_value'],
                     "aa_noise_mean": ch_data['aa_noise_mean'], "aa_frequency": ch_data['aa_frequency'],
                     "aa_pulse_cycle": ch_data['aa_pulse_cycle'], "aa_50Hz": ch_data['aa_50Hz'],
                     "aa_100Hz": ch_data['aa_100Hz']})
                alarm_information = json.dumps(
                    {"upper_alarm_amplitude": dev_para_dict['upper_alarm_amplitude_aa_ch%s' % channel_number],
                     "upper_alarm_frequency": dev_para_dict['upper_alarm_frequency_aa_ch%s' % channel_number],
                     "trend_alarm_amplitude": dev_para_dict['trend_alarm_amplitude_aa_ch%s' % channel_number],
                     "trend_alarm_frequency": dev_para_dict['trend_alarm_frequency_aa_ch%s' % channel_number],
                     "alarm_strategy": dev_para_dict['alarm_strategy_aa_ch%s' % channel_number]})
            elif channel_type == 'tev':
                content = json.dumps(
                    {"tev_peak_amp": ch_data['tev_peak_amp'], "tev_mean_value": ch_data['tev_mean_value'],
                     "tev_noise_mean": ch_data['tev_noise_mean'], "tev_frequency": ch_data['tev_frequency'],
                     "tev_pulse_cycle": ch_data['tev_pulse_cycle'], "tev_50Hz": ch_data['tev_50Hz'],
                     "tev_100Hz": ch_data['tev_100Hz']})
                alarm_information = json.dumps(
                    {"upper_alarm_amplitude": dev_para_dict['upper_alarm_amplitude_tev_ch%s' % channel_number],
                     "upper_alarm_frequency": dev_para_dict['upper_alarm_frequency_tev_ch%s' % channel_number],
                     "trend_alarm_amplitude": dev_para_dict['trend_alarm_amplitude_tev_ch%s' % channel_number],
                     "trend_alarm_frequency": dev_para_dict['trend_alarm_frequency_tev_ch%s' % channel_number],
                     "alarm_strategy": dev_para_dict['alarm_strategy_tev_ch%s' % channel_number]})
        elif device_type == '06':
            content_dict = {v.split('_ch')[0]: ch_data.get(v) for v in DeviceSix.METRICS_MAP.get(channel_type, {}).values()
                            if ch_data.get(v)}
            content = json.dumps(content_dict)

            alarm_information = json.dumps(
                {"upper_alarm_amplitude": dev_para_dict[f'upper_alarm_amplitude_{channel_type}_ch{channel_number}'],
                 "upper_alarm_frequency": dev_para_dict[f'upper_alarm_frequency_{channel_type}_ch{channel_number}'],
                 "trend_alarm_amplitude": dev_para_dict[f'trend_alarm_amplitude_{channel_type}_ch{channel_number}'],
                 "trend_alarm_frequency": dev_para_dict[f'trend_alarm_frequency_{channel_type}_ch{channel_number}'],
                 "alarm_strategy": dev_para_dict[f'alarm_strategy_{channel_type}_ch{channel_number}']})
        elif device_type in ['10', '80']:
            content = json.dumps({"temperature": ch_data['temp']})
            alarm_information = json.dumps(
                {"upper_alarm_temp": dev_para_dict['upper_alarm_temp_ch%s' % channel_number],
                 "trend_alarm_temp": dev_para_dict['trend_alarm_temp_ch%s' % channel_number],
                 "alarm_strategy": dev_para_dict['alarm_strategy_temp_ch%s' % channel_number]})
        elif device_type == '12':
            content = json.dumps({"cc": ch_data['cc']})
            alarm_information = json.dumps(
                {"upper_alarm_cc": dev_para_dict['upper_alarm_cc_ch%s' % channel_number],
                 "trend_alarm_cc": dev_para_dict['trend_alarm_cc_ch%s' % channel_number],
                 "alarm_strategy": dev_para_dict['alarm_strategy_cc_ch%s' % channel_number]})

        elif device_type == '27':  # todo 有需求再完善
            if channel_type == 'tev':
                content = json.dumps(
                    {'peak_value': ch_data['peak_value'], 'discharge_average': ch_data['discharge_average'],
                     'noise_level': ch_data['noise_level'], 'pulse_per_cycle': ch_data['pulse_per_cycle'],
                     'frequency': ch_data['frequency'], '50hz': ch_data['50_hz'], '100hz': ch_data['100_hz']})
                alarm_information = json.dumps(
                    {"upper_alarm_amplitude": dev_para_dict['upper_alarm_amplitude_tev_ch%s' % channel_number],
                     "upper_alarm_frequency": dev_para_dict['upper_alarm_frequency_tev_ch%s' % channel_number],
                     "trend_alarm_amplitude": dev_para_dict['trend_alarm_amplitude_tev_ch%s' % channel_number],
                     "trend_alarm_frequency": dev_para_dict['trend_alarm_frequency_tev_ch%s' % channel_number],
                     "alarm_strategy": dev_para_dict['alarm_strategy_tev_ch%s' % channel_number]})
            elif channel_type == 'temp':
                content = json.dumps({'env_temp': ch_data['env_temp'], 'cable_temp': ch_data['cable_temp'],
                                      'temp_diff': float('%.1f' % (ch_data['cable_temp'] - ch_data['env_temp']))})
                alarm_information = json.dumps(
                    {'alarm_strategy': dev_para_dict['alarm_strategy_temp_ch%s' % channel_number],
                     'upper_alarm_temp': dev_para_dict['upper_alarm_temp_ch%s' % channel_number],
                     'upper_alarm_temp_diff': dev_para_dict['upper_alarm_temp_diff_ch%s' % channel_number]})
            elif channel_type == 'water':
                content = json.dumps({'water_status': ch_data['water_status']})
                alarm_information = json.dumps(
                    {"alarm_strategy": dev_para_dict['alarm_strategy_water_ch%s' % channel_number]})

        if alarm_res and alarm_res[1] is None:
            id = alarm_res[0]
            alarm_counts = alarm_res[2] + 1
            val = (content, alarm_information, alarm_lv, create_time, alarm_counts, id)
            alarm_update_val.append(val)
        else:
            ch_sql = 'select id,dept_id from channel_register where device_code=%s and channel_number=%s and channel_type=%s'
            ch_val = (device_code, channel_number, channel_type)
            cursor.execute(ch_sql, ch_val)
            ch_res = cursor.fetchone()
            val = (
                ch_res[1], create_time, create_time, device_code, channel_number, content, alarm_information, alarm_lv,
                channel_type,
                ch_res[0], ch_res[1], 1)
            alarm_insert_val.append(val)

    def set_default_parameter(self,dev_info):
        """
        往redis中存默认参数
        Args:
            dev_info: 总线注册中的设备信息

        Returns: None

        """
        logging.info(dev_info)
        redis_dict = {'offline_timeout': 86400}
        if dev_info['device_type'] == '01':  # 八通道
            for i in range(1, 9):
                redis_dict['alarm_strategy_uhf_ch%s' % i] = '02'
                redis_dict['trend_calculate_days_uhf_ch%s' % i] = 7
                redis_dict['upper_alarm_amplitude_uhf_ch%s' % i] = 1000
                redis_dict['upper_alarm_frequency_uhf_ch%s' % i] = 500
                redis_dict['trend_alarm_amplitude_uhf_ch%s' % i] = 1000
                redis_dict['trend_alarm_frequency_uhf_ch%s' % i] = 500
                redis_dict['warning_counts_threshold_uhf_ch%s' % i] = 5
                redis_dict['alarm_counts_threshold_uhf_ch%s' % i] = 10

            for i in range(1, dev_info['temp_numb'] + 1):
                redis_dict['alarm_strategy_temp_ch%s' % i] = '02'
                redis_dict['trend_calculate_days_temp_ch%s' % i] = 7
                redis_dict['upper_alarm_temp_ch%s' % i] = 70
                redis_dict['trend_alarm_temp_ch%s' % i] = 70
                redis_dict['warning_counts_threshold_temp_ch%s' % i] = 5
                redis_dict['alarm_counts_threshold_temp_ch%s' % i] = 10
            redis_dict['temperature_number'] = dev_info['temp_numb']

        elif dev_info['device_type'] == '02':  # 三通道
            for i in range(1, 4):
                redis_dict['alarm_strategy_hfct_ch%s' % i] = '02'
                redis_dict['trend_calculate_days_hfct_ch%s' % i] = 7
                redis_dict['upper_alarm_amplitude_hfct_ch%s' % i] = 100
                redis_dict['upper_alarm_frequency_hfct_ch%s' % i] = 45
                redis_dict['trend_alarm_amplitude_hfct_ch%s' % i] = 100
                redis_dict['trend_alarm_frequency_hfct_ch%s' % i] = 45
                redis_dict['warning_counts_threshold_hfct_ch%s' % i] = 5
                redis_dict['alarm_counts_threshold_hfct_ch%s' % i] = 10

        elif dev_info['device_type'] == '04':
            redis_dict['alarm_strategy_aa_ch1'] = '02'
            redis_dict['trend_calculate_days_aa_ch1'] = 7
            redis_dict['upper_alarm_amplitude_aa_ch1'] = 14
            redis_dict['upper_alarm_frequency_aa_ch1'] = 10
            redis_dict['trend_alarm_amplitude_aa_ch1'] = 14
            redis_dict['trend_alarm_frequency_aa_ch1'] = 10
            redis_dict['warning_counts_threshold_aa_ch1'] = 5
            redis_dict['alarm_counts_threshold_aa_ch1'] = 10

            redis_dict['alarm_strategy_tev_ch1'] = '02'
            redis_dict['trend_calculate_days_tev_ch1'] = 7
            redis_dict['upper_alarm_amplitude_tev_ch1'] = 35
            redis_dict['upper_alarm_frequency_tev_ch1'] = 10
            redis_dict['trend_alarm_amplitude_tev_ch1'] = 35
            redis_dict['trend_alarm_frequency_tev_ch1'] = 10
            redis_dict['warning_counts_threshold_tev_ch1'] = 5
            redis_dict['alarm_counts_threshold_tev_ch1'] = 10

        elif dev_info['device_type'] == '06':
            redis_dict['alarm_strategy_aa_ch1'] = '02'
            redis_dict['trend_calculate_days_aa_ch1'] = 7
            redis_dict['upper_alarm_amplitude_aa_ch1'] = 14
            redis_dict['upper_alarm_frequency_aa_ch1'] = 10
            redis_dict['trend_alarm_amplitude_aa_ch1'] = 14
            redis_dict['trend_alarm_frequency_aa_ch1'] = 10
            redis_dict['warning_counts_threshold_aa_ch1'] = 5
            redis_dict['alarm_counts_threshold_aa_ch1'] = 10

            redis_dict['alarm_strategy_tev_ch1'] = '02'
            redis_dict['trend_calculate_days_tev_ch1'] = 7
            redis_dict['upper_alarm_amplitude_tev_ch1'] = 35
            redis_dict['upper_alarm_frequency_tev_ch1'] = 10
            redis_dict['trend_alarm_amplitude_tev_ch1'] = 35
            redis_dict['trend_alarm_frequency_tev_ch1'] = 10
            redis_dict['warning_counts_threshold_tev_ch1'] = 5
            redis_dict['alarm_counts_threshold_tev_ch1'] = 10

            redis_dict['alarm_strategy_uhf_ch1'] = '02'
            redis_dict['trend_calculate_days_uhf_ch1'] = 7
            redis_dict['upper_alarm_amplitude_uhf_ch1'] = 35
            redis_dict['upper_alarm_frequency_uhf_ch1'] = 10
            redis_dict['trend_alarm_amplitude_uhf_ch1'] = 35
            redis_dict['trend_alarm_frequency_uhf_ch1'] = 10
            redis_dict['warning_counts_threshold_uhf_ch1'] = 5
            redis_dict['alarm_counts_threshold_uhf_ch1'] = 10
        elif dev_info['device_type'] in ['10', '80']:
            for i in range(1, dev_info['temp_numb'] + 1):
                redis_dict['alarm_strategy_temp_ch%s' % i] = '02'
                redis_dict['trend_calculate_days_temp_ch%s' % i] = 7
                redis_dict['upper_alarm_temp_ch%s' % i] = 70
                redis_dict['trend_alarm_temp_ch%s' % i] = 70
                redis_dict['warning_counts_threshold_temp_ch%s' % i] = 5
                redis_dict['alarm_counts_threshold_temp_ch%s' % i] = 10
            redis_dict['temperature_number'] = dev_info['temp_numb']

        elif dev_info['device_type'] == '12':
            for i in range(1, 5):
                redis_dict['alarm_strategy_cc_ch%s' % i] = '02'
                redis_dict['trend_calculate_days_cc_ch%s' % i] = 7
                redis_dict['upper_alarm_cc_ch%s' % i] = 40
                redis_dict['trend_alarm_cc_ch%s' % i] = 40
                redis_dict['warning_counts_threshold_cc_ch%s' % i] = 5
                redis_dict['alarm_counts_threshold_cc_ch%s' % i] = 10

        elif dev_info['device_type'] == '27':  # todo 有需求再完善
            redis_dict['alarm_strategy_tev_ch1'] = '02'
            redis_dict['trend_calculate_days_tev_ch1'] = 7
            redis_dict['upper_alarm_amplitude_tev_ch1'] = 35
            redis_dict['upper_alarm_frequency_tev_ch1'] = 10
            redis_dict['trend_alarm_amplitude_tev_ch1'] = 35
            redis_dict['trend_alarm_frequency_tev_ch1'] = 10
            redis_dict['warning_counts_threshold_tev_ch1'] = 5
            redis_dict['alarm_counts_threshold_tev_ch1'] = 10

            redis_dict['alarm_strategy_water_ch1'] = '02'
            redis_dict['alarm_counts_threshold_water_ch1'] = 1

            redis_dict['alarm_strategy_temp_ch1'] = '02'
            redis_dict['alarm_strategy_temp_ch2'] = '02'
            redis_dict['trend_calculate_days_temp_ch2'] = 7
            redis_dict['upper_alarm_temp_ch2'] = 70
            redis_dict['trend_alarm_temp_ch2'] = 70
            redis_dict['upper_alarm_temp_diff_ch2'] = 50
            redis_dict['warning_counts_threshold_temp_ch2'] = 5
            redis_dict['alarm_counts_threshold_temp_ch2'] = 10

        if redis_dict:
            redis_key = pf.PROJECT_ID + ':device_info:' + dev_info['device_code']
            self.redis_conn.hmset(redis_key, mapping=redis_dict)
            logging.info('[%s]-redis set default para success' % dev_info['device_code'])
    #新增批量测温通道的需要增加注册部分
    def set_add_multiple_default_tempature_parameter(self, dev_info):
        redis_dict = {'offline_timeout': 86400}
        if dev_info['device_type'] == '01':  # 八通道
            for i in range(1, dev_info['temp_numb'] + 1):
                redis_dict['alarm_strategy_temp_ch%s' % i] = '02'
                redis_dict['trend_calculate_days_temp_ch%s' % i] = 7
                redis_dict['upper_alarm_temp_ch%s' % i] = 70
                redis_dict['trend_alarm_temp_ch%s' % i] = 70
                redis_dict['warning_counts_threshold_temp_ch%s' % i] = 5
                redis_dict['alarm_counts_threshold_temp_ch%s' % i] = 10
            redis_dict['temperature_number'] = dev_info['temp_numb']

        elif dev_info['device_type'] in ['10', '80']:
            for i in range(1, dev_info['temp_numb'] + 1):
                redis_dict['alarm_strategy_temp_ch%s' % i] = '02'
                redis_dict['trend_calculate_days_temp_ch%s' % i] = 7
                redis_dict['upper_alarm_temp_ch%s' % i] = 70
                redis_dict['trend_alarm_temp_ch%s' % i] = 70
                redis_dict['warning_counts_threshold_temp_ch%s' % i] = 5
                redis_dict['alarm_counts_threshold_temp_ch%s' % i] = 10
            redis_dict['temperature_number'] = dev_info['temp_numb']

        if redis_dict:
            redis_key = pf.PROJECT_ID + ':device_info:' + dev_info['device_code']
            self.redis_conn.hmset(redis_key, mapping=redis_dict)
            logging.info('[%s]-redis set default para success' % dev_info['device_code'])
        #新增单个测温通道的需要增加注册部分
    def set_add_Single_default_tempature_parameter(self, dev_info,channel):
        redis_dict = {}
        if dev_info['device_type'] == '01':  # 八通道
            redis_dict[f'alarm_strategy_temp_ch{channel}'] = '02'
            redis_dict[f'trend_calculate_days_temp_ch{channel}'] = 7
            redis_dict[f'upper_alarm_temp_ch{channel}'] = 70
            redis_dict[f'trend_alarm_temp_ch{channel}'] = 70
            redis_dict[f'warning_counts_threshold_temp_ch{channel}'] = 5
            redis_dict[f'alarm_counts_threshold_temp_ch{channel}'] = 10
            redis_dict['temperature_number'] = dev_info['temp_numb']

        elif dev_info['device_type'] in ['10', '80']:
            redis_dict[f'alarm_strategy_temp_ch{channel}'] = '02'
            redis_dict[f'trend_calculate_days_temp_ch{channel}'] = 7
            redis_dict[f'upper_alarm_temp_ch{channel}'] = 70
            redis_dict[f'trend_alarm_temp_ch{channel}'] = 70
            redis_dict[f'warning_counts_threshold_temp_ch{channel}'] = 5
            redis_dict[f'alarm_counts_threshold_temp_ch{channel}'] = 10
            redis_dict['temperature_number'] = dev_info['temp_numb']

        if redis_dict:
            redis_key = pf.PROJECT_ID + ':device_info:' + dev_info['device_code']
            self.redis_conn.hmset(redis_key, mapping=redis_dict)
            logging.info('[%s]-redis set default para success' % dev_info['device_code'])

    def run(self):
        logging.info('--StoreService start--')
        while True:
            try:
                redis_data = self.redis_conn.lpop(pf.REDIS_CACHE_KEY)
                if redis_data:
                    data_dict = json.loads(redis_data.decode())
                    create_time = data_dict['update_time']
                    # logging.debug(data_dict)
                    logging.debug(type(data_dict))
                    data_type = data_dict['data_type']
                    if data_type == 'register':  # 总线注册之设备通道注册
                        bus_data = json.loads(data_dict['data'])
                        logging.debug(bus_data)
                        logging.debug(type(bus_data))

                        with self.mysql_conn.cursor() as cursor:
                            sql = 'select id,connect_device,connect_type,ip_address,port from bus_register where register_id=%s'
                            val = bus_data['bus_register_id']
                            cursor.execute(sql, val)
                            sel_data = cursor.fetchone()
                            logging.debug(sel_data)

                            if sel_data:  # 已有该总线，更新(不考虑原有设备信息变更的情况)
                                # if bus_data['connect_device'] != sel_data[1] or bus_data['connect_type'] != sel_data[
                                #     2] or \
                                #         bus_data['ip_address'] != sel_data[3] or bus_data['port'] != sel_data[4]:
                                #     update_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                                #     sql = 'update bus_register set connect_device=%s,connect_type=%s,ip_address=%s,port=%s,update_time=%s'
                                #     val = (bus_data['connect_device'], bus_data['connect_type'], bus_data['ip_address'],
                                #            bus_data['port'], update_time)
                                #     cursor.execute(sql, val)
                                #     self.mysql_conn.commit()
                                #     logging.info('[%s]-update bus_register success' % bus_data['bus_register_id'])

                                id_res = sel_data[0]
                                sql = 'select device_code from device_register'
                                cursor.execute(sql)
                                dev_data = cursor.fetchall()
                                dev_list = [i[0] for i in dev_data]
                                logging.debug(dev_list)
                                for dev_info in bus_data['collector_config']:
                                    if dev_info['device_code'] in dev_list:  # 判断设备是否存在、存在再判断通道，通道不存在进行注册
                                        self.dev_ch_register(id_res, dev_info, create_time, cursor)
                                        self.mysql_conn.commit()
                                        # # 往redis中存默认参数
                                        # self.set_default_parameter(dev_info)
                                logging.info('[%s]-insert channel_register success' % bus_data[
                                    'bus_register_id'])

                            else:  # 无该总线
                                # # 总线注册
                                # bus_guid = str(uuid.uuid4()).replace('-', '')
                                # sql = 'insert into bus_register values(null,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
                                # val = (bus_data['bus_name'], bus_data['bus_register_id'], bus_guid,
                                #        bus_data['connect_device'], bus_data['connect_type'], bus_data['ip_address'],
                                #        bus_data['port'], 0, create_time, None)
                                # cursor.execute(sql, val)
                                # self.mysql_conn.commit()
                                # logging.info('[%s]-insert bus_register success' % bus_data['bus_register_id'])

                                # sql = 'select id from bus_register where register_id=%s'
                                # val = bus_data['bus_register_id']
                                # cursor.execute(sql, val)
                                # id_res = cursor.fetchone()[0]
                                # for dev_info in bus_data['collector_config']:  # 新增设备、通道注册
                                #     StoreService.dev_ch_register(id_res, dev_info, create_time, cursor)
                                #     self.mysql_conn.commit()  # 按每个设备提交一次
                                #     # 往redis中存默认参数
                                #     self.set_default_parameter(dev_info)
                                # logging.info('[%s]-insert device_register and channel_register success' % bus_data[
                                #     'bus_register_id'])
                                pass

                            # pf.mysql_create_data_tables(pf.MYSQL_CONFIG['database'], cursor)  # 建数据表

                    elif data_type == 'parameter':  # 设备参数
                        para_data = json.loads(data_dict['data'])
                        logging.debug(para_data)
                        logging.debug(type(para_data))
                        device_type = para_data['device_type']
                        device_code = para_data['device_code']
                        table_name = 'device_parameter_' + device_type
                        para_data_data = json.loads(para_data['data'])
                        logging.debug(para_data_data)
                        logging.debug(type(para_data_data))
                        para_json = json.dumps(para_data_data['para_dictionary_table'])
                        with self.mysql_conn.cursor() as cursor:
                            pf.mysql_create_para_tables(pf.MYSQL_CONFIG['database'], cursor, table_name)  # 建参数表

                            sql = 'select parameter from {} where device_code=%s'.format(table_name)
                            val = device_code
                            cursor.execute(sql, val)
                            dev_data = cursor.fetchone()
                            if dev_data:
                                if dev_data[0] != para_json:
                                    sql = 'update {} set parameter=%s'.format(table_name)
                                    val = para_json
                                    cursor.execute(sql, val)
                                    self.mysql_conn.commit()
                                    logging.info('update %s success' % table_name)
                            else:
                                sql = 'insert into {} values(null,%s,%s,%s,%s,%s,%s)'.format(table_name)
                                val = (device_code, para_json, 'regular', 0, 0, create_time)
                                cursor.execute(sql, val)
                                self.mysql_conn.commit()
                                logging.info('intert %s success' % table_name)

                        # 往redis中存设备参数
                        device_info = para_data_data['device_info']
                        logging.debug(device_info)
                        para_dict = {i['address']: i['value'] for i in para_data_data['para_dictionary_table']}
                        logging.debug(para_dict)

                        redis_dict = device_info
                        if device_type != '10':
                            redis_dict.update(para_dict)
                        redis_dict['para_update_time'] = create_time

                        if redis_dict:
                            redis_key = pf.PROJECT_ID + ':device_info:' + device_code
                            self.redis_conn.hmset(redis_key, mapping=redis_dict)
                            logging.info('[%s]-redis set para success' % device_code)

                    elif data_type == 'data':  # 设备数据
                        data = json.loads(data_dict['data'])
                        # logging.debug(data)
                        logging.debug(type(data))
                        device_type = data['device_type']
                        device_code = data['device_code']
                        data_data = json.loads(data['data'])
                        # logging.debug(data_data)
                        logging.debug(type(data_data))
                        redis_key = pf.PROJECT_ID + ':device_info:' + device_code
                        dev_para_bytes = self.redis_conn.hgetall(redis_key)
                        dev_para_dict = {k.decode(): v.decode() for k, v in dev_para_bytes.items()}
                        logging.debug(dev_para_dict)

                        with (self.mysql_conn.cursor() as cursor):
                            alarm_update_val = []
                            alarm_insert_val = []

                            if device_type == '01':
                                # 局放数据
                                channel_type = 'uhf'
                                for ch_data in data_data['01_pdData']:
                                    logging.debug(ch_data)
                                    channel_number = ch_data['channel_number']
                                    table_name = 'data_%s_%s_%s' % (device_code, channel_number, channel_type)

                                    alarm_lv = 0  # 24h之内报警
                                    outside_alarm_lv = 0  # 实时报警
                                    ################ 添加报警逻辑 ################
                                    alarm_strategy = dev_para_dict.get('alarm_strategy_uhf_ch%s' % channel_number,'02')
                                    upper_alarm_amplitude = int(
                                        dev_para_dict.get('upper_alarm_amplitude_uhf_ch%s' % channel_number,1000))
                                    upper_alarm_frequency = int(
                                        dev_para_dict.get('upper_alarm_frequency_uhf_ch%s' % channel_number,500))
                                    warning_counts_threshold = int(
                                        dev_para_dict.get('warning_counts_threshold_uhf_ch%s' % channel_number,5))
                                    alarm_counts_threshold = int(
                                        dev_para_dict.get('alarm_counts_threshold_uhf_ch%s' % channel_number,10))
                                    
                                    alarm_flag = False
                                    if alarm_strategy == '01':  # 下位机报警
                                        if ch_data['alarm'] != 0:
                                            alarm_flag = True
                                    else:  # elif alarm_strategy == '02'  # 上位机报警
                                        # corre_res = pf.calculate_correlation(ch_data['ch1_amplitudeArray'],
                                        #                                      ch_data['phaseArray'])
                                        corre_res = 1  # todo
                                        if ch_data['amplitude'] > upper_alarm_amplitude and ch_data[
                                            'frequency'] > upper_alarm_frequency and corre_res > 0.5:
                                            alarm_flag = True

                                    if alarm_flag:
                                        sql = 'select count(*) from {} where create_time between now()-interval 24 hour and now() and is_abnormal>=1'.format(
                                            table_name)
                                        cursor.execute(sql)
                                        count_res = cursor.fetchone()[0]
                                        count_res = count_res + 1
                                        logging.debug(count_res)
                                        if count_res < warning_counts_threshold:  # 异常
                                            is_abnormal = 1
                                        elif warning_counts_threshold <= count_res < alarm_counts_threshold:  # 预警
                                            is_abnormal = 2
                                            alarm_lv = 1
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       ch_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                        elif count_res >= alarm_counts_threshold:  # 报警
                                            is_abnormal = 3
                                            alarm_lv = 2
                                            outside_alarm_lv = 2
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       ch_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                    else:
                                        is_abnormal = None
                                    ################ 添加报警逻辑 ################

                                    # 往redis中存设备数据
                                    redis_dict = {'amplitude_uhf_ch%s' % channel_number: ch_data['amplitude'],
                                                  'frequency_uhf_ch%s' % channel_number: ch_data['frequency'],
                                                  'energy_uhf_ch%s' % channel_number: ch_data['energy'],
                                                  'data_update_time': create_time}
                                    if alarm_lv:
                                        redis_dict['alarm_lv_uhf_ch%s' % channel_number] = alarm_lv
                                    redis_dict['outside_alarm_lv_uhf_ch%s' % channel_number] = outside_alarm_lv
                                    self.redis_conn.hmset(redis_key, mapping=redis_dict)
                                    logging.info('[%s]-redis set data success' % device_code)
                                    # pf.mysql_create_data_tables(pf.MYSQL_CONFIG['database'], cursor)
                                    pf.mysql_create_alone_data_tables(table_name,device_type,channel_type)

                                    sql = 'insert into {} values(null,%s,%s,%s,%s,%s,%s,%s)'.format(table_name)
                                    pd_data = (
                                        ch_data['amplitude'], ch_data['frequency'], ch_data['energy'], ch_data['alarm'],
                                        create_time, None, is_abnormal)
                                    try:
                                        cursor.execute(sql, pd_data)
                                    except Exception as e:
                                        logging.error(traceback.format_exc())
                                        continue
                                    logging.info('insert %s success' % table_name)

                                    # todo tdms
                                    try:
                                        sql = 'select `key` from dvadmin_system_dept where id=(select dept_id from channel_register WHERE device_code=%s and channel_number=%s and channel_type=%s)'
                                        val = (device_code, channel_number, channel_type)
                                        cursor.execute(sql, val)
                                        dept_key = cursor.fetchone()
                                        logging.debug(dept_key)
                                        if dept_key:  # todo 项目标识
                                            path_str = '/%s/%s' % (dept_key[0], datetime.now().strftime('%Y%m%d'))
                                            local_path = sftp_lpath + path_str
                                            if not os.path.exists(local_path):
                                                os.makedirs(local_path, exist_ok=True)
                                            file_name = '/%s_%s_%s.tdms' % (device_code, channel_number, channel_type)
                                            local_file = local_path + file_name
                                            tdms_data = []
                                            # 创建一个 TDMS 文件对象
                                            with TdmsWriter(local_file, 'a') as tdms_writer:
                                                # 创建一个通道并添加数据
                                                group = 'channel%s' % channel_number
                                                tdms_data.append(
                                                    ChannelObject(group=group, channel='create_time',
                                                                  data=[str(create_time)]))
                                                tdms_data.append(ChannelObject(group=group, channel='cycle',
                                                                               data=[str(ch_data['cycleArray'])]))
                                                tdms_data.append(ChannelObject(group=group, channel='phase',
                                                                               data=[str(ch_data['phaseArray'])]))
                                                tdms_data.append(ChannelObject(group=group, channel='amplitude_ch1',
                                                                               data=[
                                                                                   str(ch_data['ch1_amplitudeArray'])]))
                                                tdms_data.append(ChannelObject(group=group, channel='amplitude_ch2',
                                                                               data=[
                                                                                   str(ch_data['ch2_amplitudeArray'])]))
                                                tdms_data.append(ChannelObject(group=group, channel='amplitude_ch3',
                                                                               data=[
                                                                                   str(ch_data['ch3_amplitudeArray'])]))
                                                tdms_writer.write_segment(tdms_data)
                                            if sftp_upload:
                                                remote_path = sftp_rpath + path_str
                                                remote_file = remote_path + file_name
                                                asyncio.run(
                                                    upload_file(sftp_host, sftp_port, sftp_user, sftp_pwd, local_file,
                                                                remote_file, remote_path))
                                    except Exception as e:
                                        logging.error(traceback.format_exc())

                                # 温度数据
                                channel_type = 'temp'
                                redis_dict = {}
                                for temp_data in data_data['01_tempData']:
                                    logging.debug(temp_data)
                                    channel_number = temp_data['channel']
                                    table_name = 'data_%s_%s_%s' % (device_code, channel_number, channel_type)

                                    alarm_lv = 0  # 24h之内报警
                                    outside_alarm_lv = 0  # 实时报警
                                    alarm_strategy = dev_para_dict.get('alarm_strategy_temp_ch%s' % channel_number,'02')
                                    upper_alarm_temp = int(
                                        dev_para_dict.get('upper_alarm_temp_ch%s' % channel_number,70))
                                    warning_counts_threshold = int(
                                        dev_para_dict.get('warning_counts_threshold_temp_ch%s' % channel_number,5))
                                    alarm_counts_threshold = int(
                                        dev_para_dict.get('alarm_counts_threshold_temp_ch%s' % channel_number,10))
                                    
                                    alarm_flag = False
                                    if alarm_strategy == '01':  # 下位机报警
                                        if temp_data['alarm'] != 0:
                                            alarm_flag = True
                                    else:  # elif alarm_strategy == '02'  # 上位机报警
                                        if temp_data['temp'] > upper_alarm_temp:
                                            alarm_flag = True

                                    if alarm_flag:
                                        sql = 'select count(*) from {} where create_time between now()-interval 24 hour and now() and is_abnormal>=1'.format(
                                            table_name)
                                        cursor.execute(sql)
                                        count_res = cursor.fetchone()[0]
                                        count_res = count_res + 1
                                        logging.debug(count_res)
                                        if count_res < warning_counts_threshold:  # 异常
                                            is_abnormal = 1
                                        elif warning_counts_threshold <= count_res < alarm_counts_threshold:  # 预警
                                            is_abnormal = 2
                                            alarm_lv = 1
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       temp_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                        elif count_res >= alarm_counts_threshold:  # 报警
                                            is_abnormal = 3
                                            alarm_lv = 2
                                            outside_alarm_lv = 2
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       temp_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                    else:
                                        is_abnormal = None

                                    # 往redis中存设备数据
                                    redis_dict['temperature_ch%s' % channel_number] = temp_data['temp']
                                    if alarm_lv:
                                        redis_dict['alarm_lv_temp_ch%s' % channel_number] = alarm_lv
                                    redis_dict['outside_alarm_lv_temp_ch%s' % channel_number] = outside_alarm_lv
                                    # pf.mysql_create_data_tables(pf.MYSQL_CONFIG['database'], cursor)
                                    pf.mysql_create_alone_data_tables(table_name,device_type,channel_type)
                                    sql = 'insert into {} values(null,%s,%s,%s,%s)'.format(table_name)
                                    temp_val = (temp_data['temp'], temp_data['alarm'], create_time, is_abnormal)
                                    try:
                                        cursor.execute(sql, temp_val)
                                    except Exception as e:
                                        logging.error(traceback.format_exc())
                                        continue
                                    # cursor.execute(sql, temp_val)
                                    logging.info('insert %s success' % table_name)

                                if redis_dict:
                                    redis_dict['data_update_time'] = create_time
                                    self.redis_conn.hmset(redis_key, mapping=redis_dict)
                                    logging.info('[%s]-redis set data success' % device_code)

                            elif device_type == '02':
                                # 局放数据
                                channel_type = 'hfct'
                                for ch_data in data_data:
                                    logging.debug(ch_data)
                                    channel_number = ch_data['channel_number']
                                    table_name = 'data_%s_%s_%s' % (device_code, channel_number, channel_type)

                                    alarm_lv = 0
                                    alarm_strategy = dev_para_dict['alarm_strategy_hfct_ch%s' % channel_number]
                                    upper_alarm_amplitude = int(
                                        dev_para_dict['upper_alarm_amplitude_hfct_ch%s' % channel_number])
                                    upper_alarm_frequency = int(
                                        dev_para_dict['upper_alarm_frequency_hfct_ch%s' % channel_number])
                                    warning_counts_threshold = int(
                                        dev_para_dict['warning_counts_threshold_hfct_ch%s' % channel_number])
                                    alarm_counts_threshold = int(
                                        dev_para_dict['alarm_counts_threshold_hfct_ch%s' % channel_number])

                                    alarm_flag = False
                                    if alarm_strategy == '01':  # 下位机报警
                                        if ch_data['alarm'] != 0:
                                            alarm_flag = True
                                    else:  # elif alarm_strategy == '02'  # 上位机报警
                                        # corre_res = pf.calculate_correlation(ch_data['ch1_amplitudeArray'],
                                        #                                      ch_data['phaseArray'])
                                        corre_res = 1  # todo
                                        if ch_data['amplitude'] > upper_alarm_amplitude and ch_data[
                                            'frequency'] > upper_alarm_frequency and corre_res > 0.5:
                                            alarm_flag = True

                                    if alarm_flag:
                                        sql = 'select count(*) from {} where create_time between now()-interval 24 hour and now() and is_abnormal>=1'.format(
                                            table_name)
                                        cursor.execute(sql)
                                        count_res = cursor.fetchone()[0]
                                        count_res = count_res + 1
                                        logging.debug(count_res)
                                        if count_res < warning_counts_threshold:  # 异常
                                            is_abnormal = 1
                                        elif warning_counts_threshold <= count_res < alarm_counts_threshold:  # 预警
                                            is_abnormal = 2
                                            alarm_lv = 1
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       ch_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                        elif count_res >= alarm_counts_threshold:  # 报警
                                            is_abnormal = 3
                                            alarm_lv = 2
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       ch_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                    else:
                                        is_abnormal = None

                                    # 往redis中存设备数据
                                    redis_dict = {'amplitude_hfct_ch%s' % channel_number: ch_data['amplitude'],
                                                  'frequency_hfct_ch%s' % channel_number: ch_data['frequency'],
                                                  'energy_hfct_ch%s' % channel_number: ch_data['energy'],
                                                  'data_update_time': create_time}
                                    if alarm_lv:
                                        redis_dict['alarm_lv_hfct_ch%s' % channel_number] = alarm_lv
                                    self.redis_conn.hmset(redis_key, mapping=redis_dict)
                                    logging.info('[%s]-redis set data success' % device_code)
                                    # pf.mysql_create_data_tables(pf.MYSQL_CONFIG['database'], cursor)
                                    pf.mysql_create_alone_data_tables(table_name,device_type,channel_type)
                                    sql = 'insert into {} values(null,%s,%s,%s,%s,%s,%s,%s)'.format(table_name)
                                    pd_data = (
                                        ch_data['amplitude'], ch_data['frequency'], ch_data['energy'], ch_data['alarm'],
                                        create_time, None, is_abnormal)
                                    cursor.execute(sql, pd_data)

                                    # todo tdms
                                    try:
                                        sql = 'select `key` from dvadmin_system_dept where id=(select dept_id from channel_register WHERE device_code=%s and channel_number=%s and channel_type=%s)'
                                        val = (device_code, channel_number, channel_type)
                                        cursor.execute(sql, val)
                                        dept_key = cursor.fetchone()
                                        logging.debug(dept_key)
                                        if dept_key:  # todo 项目标识
                                            path_str = '/%s/%s' % (dept_key[0], datetime.now().strftime('%Y%m%d'))
                                            local_path = sftp_lpath + path_str
                                            if not os.path.exists(local_path):
                                                os.makedirs(local_path, exist_ok=True)
                                            file_name = '/%s_%s_%s.tdms' % (device_code, channel_number, channel_type)
                                            local_file = local_path + file_name
                                            tdms_data = []
                                            # 创建一个 TDMS 文件对象
                                            with TdmsWriter(local_file, 'a') as tdms_writer:
                                                # 创建一个通道并添加数据
                                                group = 'channel%s' % channel_number
                                                tdms_data.append(
                                                    ChannelObject(group=group, channel='create_time',
                                                                  data=[str(create_time)]))
                                                tdms_data.append(ChannelObject(group=group, channel='cycle',
                                                                               data=[str(ch_data['cycleArray'])]))
                                                tdms_data.append(ChannelObject(group=group, channel='phase',
                                                                               data=[str(ch_data['phaseArray'])]))
                                                tdms_data.append(ChannelObject(group=group, channel='amplitude_ch1',
                                                                               data=[
                                                                                   str(ch_data['ch1_amplitudeArray'])]))
                                                tdms_data.append(ChannelObject(group=group, channel='amplitude_ch2',
                                                                               data=[
                                                                                   str(ch_data['ch2_amplitudeArray'])]))
                                                tdms_data.append(ChannelObject(group=group, channel='amplitude_ch3',
                                                                               data=[
                                                                                   str(ch_data['ch3_amplitudeArray'])]))
                                                tdms_writer.write_segment(tdms_data)
                                            if sftp_upload:
                                                remote_path = sftp_rpath + path_str
                                                remote_file = remote_path + file_name
                                                asyncio.run(
                                                    upload_file(sftp_host, sftp_port, sftp_user, sftp_pwd, local_file,
                                                                remote_file, remote_path))
                                    except Exception as e:
                                        logging.error(traceback.format_exc())

                            elif device_type == '03':
                                pass
                            elif device_type == '04':
                                logging.debug(
                                    data_data)  # {'04_data': {'aa': {'aa_map': {'aa_amp': [], 'aa_phase': []}, 'aa_data': {'aa_peak_amp': -10.0, 'aa_mean_value': -10.0, 'aa_noise_mean': -10.0, 'aa_frequency': 0, 'aa_pulse_cycle': 0, 'aa_50Hz': 0.0, 'aa_100Hz': 0.0, 'aa_alarm': 0, 'aa_forewarning': 0, 'aa_effective_value': 0.0, 'aa_noise_level': 0.0, 'aa_status': 0}}, 'tev': {'tev_map': {'tev_amp': [], 'tev_phase': []}, 'tev_data': {'tev_peak_amp': 23.7, 'tev_mean_value': 18.4, 'tev_noise_mean': 18.4, 'tev_frequency': 0, 'tev_pulse_cycle': 0, 'tev_50Hz': 0.0, 'tev_100Hz': 0.0, 'tev_alarm': 0, 'tev_forewarning': 0, 'tev_effective_value': 0.0, 'tev_noise_level': 0.0, 'tev_status': 0}}}}

                                channel_number = 1
                                for channel_type, ch_data in data_data['04_data'].items():
                                    map_data = ch_data['%s_map' % channel_type]
                                    real_data = ch_data['%s_data' % channel_type]

                                    table_name = 'data_%s_%s_%s' % (device_code, channel_number, channel_type)

                                    alarm_lv = 0
                                    alarm_strategy = dev_para_dict[
                                        'alarm_strategy_%s_ch%s' % (channel_type, channel_number)]
                                    upper_alarm_amplitude = int(
                                        dev_para_dict['upper_alarm_amplitude_%s_ch%s' % (channel_type, channel_number)])
                                    upper_alarm_frequency = int(
                                        dev_para_dict['upper_alarm_frequency_%s_ch%s' % (channel_type, channel_number)])
                                    warning_counts_threshold = int(
                                        dev_para_dict[
                                            'warning_counts_threshold_%s_ch%s' % (channel_type, channel_number)])
                                    alarm_counts_threshold = int(
                                        dev_para_dict[
                                            'alarm_counts_threshold_%s_ch%s' % (channel_type, channel_number)])

                                    alarm_flag = False
                                    if alarm_strategy == '01':  # 下位机报警
                                        if real_data['%s_alarm' % channel_type] != 0:
                                            alarm_flag = True
                                    else:  # elif alarm_strategy == '02'  # 上位机报警
                                        # corre_res = pf.calculate_correlation(map_data['%s_amp' % channel_type],
                                        #                                      map_data['%s_phase' % channel_type])
                                        corre_res = 1  # todo
                                        if real_data['%s_peak_amp' % channel_type] > upper_alarm_amplitude and \
                                                real_data[
                                                    '%s_frequency' % channel_type] > upper_alarm_frequency and corre_res > 0.5:
                                            alarm_flag = True

                                    if alarm_flag:
                                        sql = 'select count(*) from {} where create_time between now()-interval 24 hour and now() and is_abnormal>=1'.format(
                                            table_name)
                                        cursor.execute(sql)
                                        count_res = cursor.fetchone()[0]
                                        count_res = count_res + 1
                                        logging.debug(count_res)
                                        if count_res < warning_counts_threshold:  # 异常
                                            is_abnormal = 1
                                        elif warning_counts_threshold <= count_res < alarm_counts_threshold:  # 预警
                                            is_abnormal = 2
                                            alarm_lv = 1
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       real_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                        elif count_res >= alarm_counts_threshold:  # 报警
                                            is_abnormal = 3
                                            alarm_lv = 2
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       real_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                    else:
                                        is_abnormal = None

                                    # 往redis中存设备数据
                                    redis_dict = {'data_update_time': create_time}
                                    redis_dict['amplitude_%s_ch1' % channel_type] = real_data[
                                        '%s_peak_amp' % channel_type]
                                    redis_dict['mean_value_%s_ch1' % channel_type] = real_data[
                                        '%s_mean_value' % channel_type]
                                    redis_dict['noise_mean_%s_ch1' % channel_type] = real_data[
                                        '%s_noise_mean' % channel_type]
                                    redis_dict['frequency_%s_ch1' % channel_type] = real_data[
                                        '%s_frequency' % channel_type]
                                    redis_dict['pulse_cycle_%s_ch1' % channel_type] = real_data[
                                        '%s_pulse_cycle' % channel_type]
                                    redis_dict['50Hz_%s_ch1' % channel_type] = real_data['%s_50Hz' % channel_type]
                                    redis_dict['100Hz_%s_ch1' % channel_type] = real_data['%s_100Hz' % channel_type]
                                    redis_dict['alarm_%s_ch1' % channel_type] = real_data['%s_alarm' % channel_type]
                                    redis_dict['forewarning_%s_ch1' % channel_type] = real_data[
                                        '%s_forewarning' % channel_type]
                                    redis_dict['effective_value_%s_ch1' % channel_type] = real_data[
                                        '%s_effective_value' % channel_type]
                                    redis_dict['noise_level_%s_ch1' % channel_type] = real_data[
                                        '%s_noise_level' % channel_type]
                                    redis_dict['status_%s_ch1' % channel_type] = real_data['%s_status' % channel_type]
                                    if alarm_lv:
                                        redis_dict['alarm_lv_%s_ch%s' % (channel_type, channel_number)] = alarm_lv
                                    self.redis_conn.hmset(redis_key, mapping=redis_dict)
                                    logging.info('[%s]-redis set data success' % device_code)
                                    # pf.mysql_create_data_tables(pf.MYSQL_CONFIG['database'], cursor)
                                    pf.mysql_create_alone_data_tables(table_name,device_type,channel_type)
                                    sql = 'insert into {} values(null,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'.format(
                                        table_name)
                                    pd_data = list(real_data.values())
                                    pd_data.append(create_time)
                                    pd_data.append(is_abnormal)
                                    cursor.execute(sql, pd_data)

                                    # todo tdms
                                    try:
                                        sql = 'select `key` from dvadmin_system_dept where id=(select dept_id from channel_register WHERE device_code=%s and channel_number=%s and channel_type=%s)'
                                        val = (device_code, channel_number, channel_type)
                                        cursor.execute(sql, val)
                                        dept_key = cursor.fetchone()
                                        logging.debug(dept_key)
                                        if dept_key:  # todo 项目标识
                                            path_str = '/%s/%s' % (dept_key[0], datetime.now().strftime('%Y%m%d'))
                                            local_path = sftp_lpath + path_str
                                            if not os.path.exists(local_path):
                                                os.makedirs(local_path, exist_ok=True)
                                            file_name = '/%s_%s_%s.tdms' % (device_code, channel_number, channel_type)
                                            local_file = local_path + file_name
                                            tdms_data = []
                                            # 创建一个 TDMS 文件对象
                                            with TdmsWriter(local_file, 'a') as tdms_writer:
                                                # 创建一个通道并添加数据
                                                group = 'channel%s' % channel_number
                                                tdms_data.append(
                                                    ChannelObject(group=group, channel='create_time',
                                                                  data=[str(create_time)]))
                                                tdms_data.append(ChannelObject(group=group, channel='cycle',
                                                                               data=[str(map_data[
                                                                                             '%s_cycle' % channel_type])]))
                                                tdms_data.append(ChannelObject(group=group, channel='phase',
                                                                               data=[str(map_data[
                                                                                             '%s_phase' % channel_type])]))
                                                tdms_data.append(ChannelObject(group=group, channel='amplitude_ch1',
                                                                               data=[
                                                                                   str(map_data[
                                                                                           '%s_amp' % channel_type])]))
                                                tdms_writer.write_segment(tdms_data)
                                            if sftp_upload:
                                                remote_path = sftp_rpath + path_str
                                                remote_file = remote_path + file_name
                                                asyncio.run(
                                                    upload_file(sftp_host, sftp_port, sftp_user, sftp_pwd, local_file,
                                                                remote_file, remote_path))
                                    except Exception as e:
                                        logging.error(traceback.format_exc())
                            elif device_type == '06':
                                # logging.debug(
                                #     data_data)  # {'06_data': {'aaData': {'aaMap': {'amplitude': [], 'phase': []}, 'aa_data': {'aa_peak_amp': -10.0, 'aa_mean_value': -10.0, 'aa_noise_mean': -10.0, 'aa_frequency': 0, 'aa_pulse_cycle': 0, 'aa_50Hz': 0.0, 'aa_100Hz': 0.0, 'aa_alarm': 0, 'aa_forewarning': 0, 'aa_effective_value': 0.0, 'aa_noise_level': 0.0, 'aa_status': 0}}, 'tev': {'tev_map': {'tev_amp': [], 'tev_phase': []}, 'tev_data': {'tev_peak_amp': 23.7, 'tev_mean_value': 18.4, 'tev_noise_mean': 18.4, 'tev_frequency': 0, 'tev_pulse_cycle': 0, 'tev_50Hz': 0.0, 'tev_100Hz': 0.0, 'tev_alarm': 0, 'tev_forewarning': 0, 'tev_effective_value': 0.0, 'tev_noise_level': 0.0, 'tev_status': 0}}}}

                                channel_number = 1
                                for channel_type, ch_data in data_data['06_data'].items():
                                    map_data = ch_data['%s_map' % channel_type]
                                    real_data = ch_data['%s_data' % channel_type]

                                    table_name = 'data_%s_%s_%s' % (device_code, channel_number, channel_type)

                                    alarm_lv = 0
                                    alarm_strategy = dev_para_dict[
                                        'alarm_strategy_%s_ch%s' % (channel_type, channel_number)]
                                    upper_alarm_amplitude = int(
                                        dev_para_dict['upper_alarm_amplitude_%s_ch%s' % (channel_type, channel_number)])
                                    upper_alarm_frequency = int(
                                        dev_para_dict['upper_alarm_frequency_%s_ch%s' % (channel_type, channel_number)])
                                    warning_counts_threshold = int(
                                        dev_para_dict[
                                            'warning_counts_threshold_%s_ch%s' % (channel_type, channel_number)])
                                    alarm_counts_threshold = int(
                                        dev_para_dict[
                                            'alarm_counts_threshold_%s_ch%s' % (channel_type, channel_number)])
                                    history_data = {'create_time': create_time}
                                    history_data.update(real_data)
                                    keys = [k.split(f'_{channel_type}')[0] for k in history_data.keys()] + ['is_abnormal']
                                    pf.ensure_table_exists(cursor, table_name, keys)
                                    alarm_flag = False
                                    if alarm_strategy == '01':  # 下位机报警
                                        if real_data['%s_alarm' % channel_type] != 0:
                                            alarm_flag = True
                                    else:  # elif alarm_strategy == '02'  # 上位机报警
                                        # corre_res = pf.calculate_correlation(map_data['%s_amp' % channel_type],
                                        #                                      map_data['%s_phase' % channel_type])
                                        corre_res = 1  # todo
                                        if real_data['amplitude_%s_ch1' % channel_type] > upper_alarm_amplitude and \
                                                real_data[
                                                    'frequency_%s_ch1' % channel_type] > upper_alarm_frequency and corre_res > 0.5:
                                            alarm_flag = True

                                    if alarm_flag:
                                        sql = 'select count(*) from {} where create_time between now()-interval 24 hour and now() and is_abnormal>=1'.format(
                                            table_name)
                                        cursor.execute(sql)
                                        count_res = cursor.fetchone()[0]
                                        count_res = count_res + 1
                                        logging.debug(count_res)
                                        if count_res < warning_counts_threshold:  # 异常
                                            is_abnormal = 1
                                        elif warning_counts_threshold <= count_res < alarm_counts_threshold:  # 预警
                                            is_abnormal = 2
                                            alarm_lv = 1
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       real_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                        elif count_res >= alarm_counts_threshold:  # 报警
                                            is_abnormal = 3
                                            alarm_lv = 2
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       real_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                    else:
                                        is_abnormal = None

                                    # 往redis中存设备数据

                                    redis_dict = {'data_update_time': create_time}
                                    redis_dict.update(real_data)

                                    if alarm_lv:
                                        redis_dict['alarm_lv_%s_ch%s' % (channel_type, channel_number)] = alarm_lv
                                    self.redis_conn.hmset(redis_key, mapping=redis_dict)
                                    logging.info('[%s]-redis set data success' % device_code)


                                    data = list(history_data.values()) + [is_abnormal]
                                    logging.debug(data)
                                    logging.debug(keys)
                                    columns = ', '.join(keys)
                                    placeholders = ', '.join(
                                        ['%s'] * len(keys))  # +2 是为了包含 is_abnormal
                                    # 构建完整的数据列表
                                    # 构建完整的 SQL 语句
                                    sql = f'insert into {table_name} ({columns}) values({placeholders})'


                                    pf.ensure_table_exists(cursor, table_name, keys)
                                    # 执行 SQL 语句
                                    cursor.execute(sql, data)

                                    # todo tdms
                                    try:
                                        sql = 'select `key` from dvadmin_system_dept where id=(select dept_id from channel_register WHERE device_code=%s and channel_number=%s and channel_type=%s)'
                                        val = (device_code, channel_number, channel_type)
                                        cursor.execute(sql, val)
                                        dept_key = cursor.fetchone()
                                        logging.debug(dept_key)
                                        if dept_key:  # todo 项目标识
                                            path_str = '/%s/%s' % (dept_key[0], datetime.now().strftime('%Y%m%d'))
                                            local_path = sftp_lpath + path_str
                                            if not os.path.exists(local_path):
                                                os.makedirs(local_path, exist_ok=True)
                                            file_name = '/%s_%s_%s.tdms' % (device_code, channel_number, channel_type)
                                            local_file = local_path + file_name
                                            tdms_data = []
                                            # 创建一个 TDMS 文件对象
                                            with TdmsWriter(local_file, 'a') as tdms_writer:
                                                # 创建一个通道并添加数据
                                                group = 'channel%s' % channel_number
                                                tdms_data.append(
                                                    ChannelObject(group=group, channel='create_time',
                                                                  data=[str(create_time)]))
                                                tdms_data.append(ChannelObject(group=group, channel='cycle',
                                                                               data=[str(map_data[
                                                                                             '%s_cycle' % channel_type])]))
                                                tdms_data.append(ChannelObject(group=group, channel='phase',
                                                                               data=[str(map_data[
                                                                                             '%s_phase' % channel_type])]))
                                                tdms_data.append(ChannelObject(group=group, channel='amplitude_ch1',
                                                                               data=[
                                                                                   str(map_data[
                                                                                           '%s_amp' % channel_type])]))
                                                tdms_writer.write_segment(tdms_data)
                                            if sftp_upload:
                                                remote_path = sftp_rpath + path_str
                                                remote_file = remote_path + file_name
                                                asyncio.run(
                                                    upload_file(sftp_host, sftp_port, sftp_user, sftp_pwd, local_file,
                                                                remote_file, remote_path))
                                    except Exception as e:
                                        logging.error(traceback.format_exc())

                            elif device_type == '10':
                                # 温度数据
                                channel_type = 'temp'
                                redis_dict = {}
                                for temp_data in data_data:
                                    logging.debug(temp_data)
                                    channel_number = temp_data['channel_number']
                                    table_name = 'data_%s_%s_%s' % (device_code, channel_number, channel_type)

                                    alarm_lv = 0  # 24h之内报警
                                    outside_alarm_lv = 0  # 实时报警
                                    alarm_strategy = dev_para_dict['alarm_strategy_temp_ch%s' % channel_number]
                                    upper_alarm_temp = int(
                                        dev_para_dict['upper_alarm_temp_ch%s' % channel_number])
                                    warning_counts_threshold = int(
                                        dev_para_dict['warning_counts_threshold_temp_ch%s' % channel_number])
                                    alarm_counts_threshold = int(
                                        dev_para_dict['alarm_counts_threshold_temp_ch%s' % channel_number])

                                    alarm_flag = False
                                    if alarm_strategy == '01':  # 下位机报警
                                        if temp_data['alarm'] != 0:
                                            alarm_flag = True
                                    else:  # elif alarm_strategy == '02'  # 上位机报警
                                        if temp_data['temp'] > upper_alarm_temp:
                                            alarm_flag = True

                                    if alarm_flag:
                                        sql = 'select count(*) from {} where create_time between now()-interval 24 hour and now() and is_abnormal>=1'.format(
                                            table_name)
                                        cursor.execute(sql)
                                        count_res = cursor.fetchone()[0]
                                        count_res = count_res + 1
                                        logging.debug(count_res)
                                        if count_res < warning_counts_threshold:  # 异常
                                            is_abnormal = 1
                                        elif warning_counts_threshold <= count_res < alarm_counts_threshold:  # 预警
                                            is_abnormal = 2
                                            alarm_lv = 1
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       temp_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                        elif count_res >= alarm_counts_threshold:  # 报警
                                            is_abnormal = 3
                                            alarm_lv = 2
                                            outside_alarm_lv = 2
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       temp_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                    else:
                                        is_abnormal = None
                                    # pf.mysql_create_data_tables(pf.MYSQL_CONFIG['database'], cursor)
                                    pf.mysql_create_alone_data_tables(table_name,device_type,channel_type)
                                    sql = 'insert into {} values(null,%s,%s,%s,%s)'.format(table_name)
                                    temp_val = (temp_data['temp'], temp_data['alarm'], create_time, is_abnormal)
                                    cursor.execute(sql, temp_val)
                                    logging.info('insert %s success' % table_name)

                                    redis_dict['temperature_ch%s' % channel_number] = temp_data['temp']
                                    if alarm_lv:
                                        redis_dict['alarm_lv_temp_ch%s' % channel_number] = alarm_lv
                                    redis_dict['outside_alarm_lv_temp_ch%s' % channel_number] = outside_alarm_lv

                                # 往redis中存设备数据
                                redis_dict['data_update_time'] = create_time
                                if redis_dict:
                                    self.redis_conn.hmset(redis_key, mapping=redis_dict)
                                    logging.info('[%s]-redis set data success' % device_code)

                            elif device_type == '12':
                                # 环流数据
                                channel_type = 'cc'
                                for ch_data in data_data:
                                    logging.debug(ch_data)
                                    channel_number = ch_data['channel_number']
                                    table_name = 'data_%s_%s_%s' % (device_code, channel_number, channel_type)

                                    alarm_lv = 0  # 24h之内报警
                                    # outside_alarm_lv = 0  # 实时报警
                                    ################ 添加报警逻辑 ################
                                    alarm_strategy = dev_para_dict['alarm_strategy_cc_ch%s' % channel_number]
                                    upper_alarm_cc = int(dev_para_dict['upper_alarm_cc_ch%s' % channel_number])
                                    warning_counts_threshold = int(
                                        dev_para_dict['warning_counts_threshold_cc_ch%s' % channel_number])
                                    alarm_counts_threshold = int(
                                        dev_para_dict['alarm_counts_threshold_cc_ch%s' % channel_number])

                                    alarm_flag = False
                                    if alarm_strategy == '01':  # 下位机报警
                                        if ch_data['alarm'] != 0:
                                            alarm_flag = True
                                    else:  # elif alarm_strategy == '02'  # 上位机报警
                                        if ch_data['cc'] > upper_alarm_cc:
                                            alarm_flag = True

                                    if alarm_flag:
                                        sql = 'select count(*) from {} where create_time between now()-interval 24 hour and now() and is_abnormal>=1'.format(
                                            table_name)
                                        cursor.execute(sql)
                                        count_res = cursor.fetchone()[0]
                                        count_res = count_res + 1
                                        logging.debug(count_res)
                                        if count_res < warning_counts_threshold:  # 异常
                                            is_abnormal = 1
                                        elif warning_counts_threshold <= count_res < alarm_counts_threshold:  # 预警
                                            is_abnormal = 2
                                            alarm_lv = 1
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       ch_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                        elif count_res >= alarm_counts_threshold:  # 报警
                                            is_abnormal = 3
                                            alarm_lv = 2
                                            # outside_alarm_lv = 2
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       ch_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                    else:
                                        is_abnormal = None
                                    ################ 添加报警逻辑 ################

                                    redis_dict = {'cc_ch%s' % channel_number: ch_data['cc']}
                                    if alarm_lv:
                                        redis_dict['alarm_lv_%s_ch%s' % (channel_type,channel_number)] = alarm_lv
                                    # redis_dict['outside_alarm_lv_ch%s' % channel_number] = outside_alarm_lv
                                    # 往redis中存设备数据
                                    redis_dict['data_update_time'] = create_time
                                    if redis_dict:
                                        self.redis_conn.hmset(redis_key, mapping=redis_dict)
                                        logging.info('[%s]-redis set data success' % device_code)
                                    # pf.mysql_create_data_tables(pf.MYSQL_CONFIG['database'], cursor)
                                    pf.mysql_create_alone_data_tables(table_name,device_type,channel_type)
                                    sql = 'insert into {} values(null,%s,%s,%s,%s)'.format(table_name)
                                    cc_data = (ch_data['cc'], ch_data['alarm'], create_time, is_abnormal)
                                    cursor.execute(sql, cc_data)

                            elif device_type == '27':  # todo 有需求再完善
                                # pf.mysql_create_data_tables(pf.MYSQL_CONFIG['database'], cursor)
                                voltage_table_name = 'data_%s_voltage' % device_code
                                voltage_sql = 'insert into {} values(null,%s,%s,%s,%s)'.format(voltage_table_name)
                                voltage_val_list = []

                                tev_table_name = 'data_%s_tev' % device_code
                                tev_sql = 'insert into {} values(null,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'.format(
                                    tev_table_name)

                                temp_table_name = 'data_%s_temp' % device_code
                                temp_sql = 'insert into {} values(null,%s,%s,%s,%s,%s,%s,%s,%s)'.format(temp_table_name)

                                water_table_name = 'data_%s_water' % device_code
                                water_sql = 'insert into {} values(null,%s,%s,%s)'.format(water_table_name)

                                alarm_lv_tev = 0
                                alarm_lv_temp_ch2 = 0
                                alarm_lv_water = 0
                                alarm_strategy_tev = dev_para_dict['alarm_strategy_tev_ch1']
                                for idx in range(
                                        len(data_data)):  # [{'data_time': data_time,'voltage_data':{},...},{}...]
                                    alarm_update_val = []
                                    alarm_insert_val = []

                                    ch_data = data_data[idx]
                                    real_data_time = ch_data['data_time']  # 真实的数据时间
                                    voltage_data = ch_data['voltage_data']  # 只存数据，不存alarm_list
                                    tev_data = ch_data['tev_data']
                                    temp_data = ch_data['temp_data']
                                    water_data = ch_data['water_data']

                                    # 电压
                                    voltage_val = (voltage_data['voltage'], voltage_data['alarm'], real_data_time, None)
                                    voltage_val_list.append(voltage_val)

                                    # tev
                                    channel_type = 'tev'
                                    alarm_flag = False
                                    if alarm_strategy_tev == '01':  # 下位机报警
                                        if tev_data['alarm'] != 0:
                                            alarm_flag = True
                                    else:  # elif alarm_strategy == '02'  # 上位机报警
                                        if tev_data['peak_value'] > int(
                                                dev_para_dict['upper_alarm_amplitude_tev_ch1']) and \
                                                tev_data['frequency'] > int(
                                            dev_para_dict['upper_alarm_frequency_tev_ch1']) and int(tev_data['50_hz']) + \
                                                int(tev_data['100_hz']) > 50:
                                            alarm_flag = True

                                    if alarm_flag:
                                        channel_number = 1
                                        sql = 'select count(*) from {} where create_time between now()-interval 24 hour and now() and is_abnormal>=1'.format(
                                            tev_table_name)
                                        cursor.execute(sql)
                                        count_res = cursor.fetchone()[0]
                                        count_res = count_res + 1
                                        logging.debug(count_res)
                                        if count_res < int(dev_para_dict['warning_counts_threshold_tev_ch1']):  # 异常
                                            is_abnormal = 1
                                        elif int(dev_para_dict['warning_counts_threshold_tev_ch1']) <= count_res < int(
                                                dev_para_dict['alarm_counts_threshold_tev_ch1']):  # 预警
                                            is_abnormal = 2
                                            alarm_lv_tev = 1
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv_tev,
                                                                       tev_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, real_data_time)
                                        elif count_res >= int(dev_para_dict['alarm_counts_threshold_tev_ch1']):  # 报警
                                            is_abnormal = 3
                                            alarm_lv_tev = 2
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv_tev,
                                                                       tev_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, real_data_time)
                                    else:
                                        is_abnormal = None
                                    tev_val = (tev_data['peak_value'], tev_data['discharge_average'],
                                               tev_data['noise_level'], tev_data['pulse_per_cycle'],
                                               tev_data['frequency'],
                                               tev_data['50_hz'], tev_data['100_hz'], tev_data['warning'],
                                               tev_data['alarm'],
                                               real_data_time, is_abnormal)
                                    cursor.execute(tev_sql, tev_val)

                                    # 温度
                                    channel_type = 'temp'
                                    for channel_number in range(1, 3):
                                        if channel_number == 1:
                                            temp_val = (
                                                channel_number, temp_data['env_temp'],
                                                0, 0, 0, 0,
                                                real_data_time, None)
                                            cursor.execute(temp_sql, temp_val)
                                        else:  # elif channel_number == 2
                                            alarm_flag = False
                                            alarm_strategy = dev_para_dict['alarm_strategy_temp_ch%s' % channel_number]
                                            if alarm_strategy == '01':  # 下位机报警
                                                if temp_data['cable_temp_alarm'] != 0:
                                                    alarm_flag = True
                                            else:  # elif alarm_strategy == '02'  # 上位机报警
                                                temp_value = temp_data['cable_temp']
                                                temp_diff = temp_data['cable_temp'] - temp_data['env_temp']  # 温差
                                                if temp_value > int(dev_para_dict[
                                                                        'upper_alarm_temp_ch%s' % channel_number]) or temp_diff > int(
                                                    dev_para_dict['upper_alarm_temp_diff_ch%s' % channel_number]):
                                                    alarm_flag = True

                                            if alarm_flag:
                                                sql = 'select count(*) from {} where create_time between now()-interval 24 hour and now() and is_abnormal>=1 and channel_number=%s'.format(
                                                    temp_table_name)
                                                val = channel_number
                                                cursor.execute(sql, val)
                                                count_res = cursor.fetchone()[0]
                                                count_res = count_res + 1
                                                logging.debug(count_res)
                                                if count_res < int(dev_para_dict[
                                                                       'warning_counts_threshold_temp_ch%s' % channel_number]):  # 异常
                                                    is_abnormal = 1
                                                elif int(dev_para_dict[
                                                             'warning_counts_threshold_temp_ch%s' % channel_number]) <= count_res < int(
                                                    dev_para_dict[
                                                        'alarm_counts_threshold_temp_ch%s' % channel_number]):  # 预警
                                                    is_abnormal = 2
                                                    alarm_lv = 1
                                                    alarm_lv_temp_ch2 = 1
                                                    StoreService.alarm_operate(cursor, device_type, device_code,
                                                                               channel_number,
                                                                               channel_type, alarm_lv,
                                                                               temp_data, dev_para_dict,
                                                                               alarm_update_val,
                                                                               alarm_insert_val, real_data_time)
                                                elif count_res >= int(dev_para_dict[
                                                                          'alarm_counts_threshold_temp_ch%s' % channel_number]):  # 报警
                                                    is_abnormal = 3
                                                    alarm_lv = 2
                                                    alarm_lv_temp_ch2 = 2
                                                    StoreService.alarm_operate(cursor, device_type, device_code,
                                                                               channel_number,
                                                                               channel_type, alarm_lv,
                                                                               temp_data, dev_para_dict,
                                                                               alarm_update_val,
                                                                               alarm_insert_val, real_data_time)
                                            else:
                                                is_abnormal = None

                                            temp_val = (
                                                channel_number, temp_data['cable_temp'],
                                                temp_data['cable_temp_warning'],
                                                temp_data['cable_temp_alarm'],
                                                temp_data['cable_temp_diff_warning'],
                                                temp_data['cable_temp_diff_alarm'],
                                                real_data_time, is_abnormal)
                                            cursor.execute(temp_sql, temp_val)

                                    # 水浸
                                    channel_type = 'water'
                                    channel_number = 1
                                    if water_data['water_status']:
                                        sql = 'select count(*) from {} where create_time between now()-interval 24 hour and now() and is_abnormal>=1'.format(
                                            water_table_name)
                                        cursor.execute(sql)
                                        count_res = cursor.fetchone()[0]
                                        count_res = count_res + 1
                                        logging.debug(count_res)
                                        if count_res < int(dev_para_dict[
                                                               'alarm_counts_threshold_water_ch%s' % channel_number]):  # 异常
                                            is_abnormal = 1
                                        elif count_res >= int(dev_para_dict[
                                                                  'alarm_counts_threshold_water_ch%s' % channel_number]):  # 报警
                                            is_abnormal = 3
                                            alarm_lv_water = 2
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv_water,
                                                                       water_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, real_data_time)
                                    else:
                                        is_abnormal = None
                                    water_val = (water_data['water_status'], real_data_time, is_abnormal)
                                    cursor.execute(water_sql, water_val)
                          
                                    if alarm_update_val:
                                        sql = 'update alarm_list set content=%s,alarm_information=%s,alarm_lv=%s,update_time=%s where id=%s'
                                        cursor.executemany(sql, alarm_update_val)
                                    if alarm_insert_val:
                                        sql = 'insert into alarm_list values(null,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
                                        cursor.executemany(sql, alarm_insert_val)

                                    self.mysql_conn.commit()
                                    logging.info('insert data_%s_xxx success' % device_code)

                                    # 往redis中存设备数据
                                    if idx == len(data_data) - 1:  # 取最新一包的数据
                                        redis_dict = {'voltage': voltage_data['voltage'],
                                                      'peak_value': tev_data['peak_value'],
                                                      'discharge_average': tev_data['discharge_average'],
                                                      'noise_level': tev_data['noise_level'],
                                                      'pulse_per_cycle': tev_data['pulse_per_cycle'],
                                                      'frequency': tev_data['frequency'],
                                                      '50hz': tev_data['50_hz'],
                                                      '100hz': tev_data['100_hz'],
                                                      'temperature_ch1': temp_data['env_temp'],
                                                      'temperature_ch2': temp_data['cable_temp'],
                                                      'water_status': water_data['water_status'],
                                                      'data_update_time': real_data_time}

                                        if alarm_lv_tev:
                                            redis_dict['alarm_lv_tev_ch1'] = alarm_lv_tev
                                        if alarm_lv_temp_ch2:
                                            redis_dict['alarm_lv_temp_ch2'] = alarm_lv_temp_ch2
                                        if alarm_lv_water:
                                            redis_dict['alarm_lv_water_ch1'] = alarm_lv_water
                                        self.redis_conn.hmset(redis_key, mapping=redis_dict)
                                        logging.info('[%s]-redis set data success' % device_code)

                                if voltage_val_list:
                                    cursor.executemany(voltage_sql, voltage_val_list)

                            elif device_type == '80':
                                # 温度数据
                                channel_type = 'temp'
                                redis_dict = {}
                                for temp_data in data_data:
                                    logging.debug(temp_data)
                                    channel_number = temp_data['channel_number']
                                    table_name = 'data_%s_%s_%s' % (device_code, channel_number, channel_type)

                                    alarm_lv = 0  # 24h之内报警
                                    outside_alarm_lv = 0  # 实时报警
                                    alarm_strategy = dev_para_dict['alarm_strategy_temp_ch%s' % channel_number]
                                    upper_alarm_temp = int(
                                        dev_para_dict['upper_alarm_temp_ch%s' % channel_number])
                                    warning_counts_threshold = int(
                                        dev_para_dict['warning_counts_threshold_temp_ch%s' % channel_number])
                                    alarm_counts_threshold = int(
                                        dev_para_dict['alarm_counts_threshold_temp_ch%s' % channel_number])

                                    alarm_flag = False
                                    if alarm_strategy == '01':  # 下位机报警
                                        if temp_data['alarm'] != 0:
                                            alarm_flag = True
                                    else:  # elif alarm_strategy == '02'  # 上位机报警
                                        if temp_data['temp'] > upper_alarm_temp:
                                            alarm_flag = True

                                    if alarm_flag:
                                        sql = 'select count(*) from {} where create_time between now()-interval 24 hour and now() and is_abnormal>=1'.format(
                                            table_name)
                                        cursor.execute(sql)
                                        count_res = cursor.fetchone()[0]
                                        count_res = count_res + 1
                                        logging.debug(count_res)
                                        if count_res < warning_counts_threshold:  # 异常
                                            is_abnormal = 1
                                        elif warning_counts_threshold <= count_res < alarm_counts_threshold:  # 预警
                                            is_abnormal = 2
                                            alarm_lv = 1
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       temp_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                        elif count_res >= alarm_counts_threshold:  # 报警
                                            is_abnormal = 3
                                            alarm_lv = 2
                                            outside_alarm_lv = 2
                                            StoreService.alarm_operate(cursor, device_type, device_code,
                                                                       channel_number,
                                                                       channel_type, alarm_lv,
                                                                       temp_data, dev_para_dict,
                                                                       alarm_update_val,
                                                                       alarm_insert_val, create_time)
                                    else:
                                        is_abnormal = None
                                    # pf.mysql_create_data_tables(pf.MYSQL_CONFIG['database'], cursor)
                                    pf.mysql_create_alone_data_tables(table_name,device_type,channel_type)
                                    sql = 'insert into {} values(null,%s,%s,%s,%s)'.format(table_name)
                                    temp_val = (temp_data['temp'], temp_data['alarm'], create_time, is_abnormal)
                                    cursor.execute(sql, temp_val)
                                    logging.info('insert %s success' % table_name)

                                    redis_dict['temperature_ch%s' % channel_number] = temp_data['temp']
                                    if alarm_lv:
                                        redis_dict['alarm_lv_temp_ch%s' % channel_number] = alarm_lv
                                    redis_dict['outside_alarm_lv_temp_ch%s' % channel_number] = outside_alarm_lv

                                # 往redis中存设备数据
                                redis_dict['data_update_time'] = create_time
                                if redis_dict:
                                    self.redis_conn.hmset(redis_key, mapping=redis_dict)
                                    logging.info('[%s]-redis set data success' % device_code)

                            if alarm_update_val:
                                sql = 'update alarm_list set content=%s,alarm_information=%s,alarm_lv=%s,update_datetime=%s,alarm_counts=%s where id=%s'
                                cursor.executemany(sql, alarm_update_val)
                            if alarm_insert_val:
                                sql = 'insert into alarm_list values(null,null,%s,%s,%s,null,%s,%s,%s,%s,%s,null,null,null,0,null,%s,null,%s,null,%s,%s)'
                                #                                                          id                              is_del
                                cursor.executemany(sql, alarm_insert_val)

                        self.mysql_conn.commit()
                        logging.info('insert data_%s_xxx success' % device_code)
                else:
                    time.sleep(0.5)
            except redis.RedisError as e:  # 处理redis异常
                logging.error(traceback.format_exc())
                self.redis_conn.close()
                logging.info('redis_conn closed')
                time.sleep(60)
                self.redis_conn = pf.redis_connect()
                logging.info('redis reconnect')
            except pymysql.MySQLError as e:  # 处理mysql异常
                logging.error(traceback.format_exc())
                try:
                    self.mysql_conn.close()
                    logging.info('mysql_conn closed')
                except Exception as e:
                    logging.error(traceback.format_exc())
                time.sleep(60)
                self.mysql_conn = pf.mysql_connect()
                logging.info('mysql reconnect')
            except Exception as e:
                logging.error(traceback.format_exc())
                time.sleep(60)


if __name__ == '__main__':
    try:
        store_thread = StoreService(REDIS_CONN, MYSQL_CONN)
        store_thread.start()
    except Exception as e:
        logging.error(traceback.format_exc())
